package com.education.ai.dao.impl;

import com.education.ai.dao.AbilityAssessmentDao;
import com.education.ai.entity.AbilityAssessment;
import com.education.ai.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 能力评估数据访问实现类
 */
@Repository
public class AbilityAssessmentDaoImpl implements AbilityAssessmentDao {

    private static final Logger logger = LoggerFactory.getLogger(AbilityAssessmentDaoImpl.class);
    
    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<AbilityAssessment> findAll() {
        String sql = "SELECT * FROM ability_assessments";
        return mapResultsToAssessments(jdbcUtil.executeQuery(sql));
    }

    @Override
    public Optional<AbilityAssessment> findById(Long id) {
        String sql = "SELECT * FROM ability_assessments WHERE id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, id);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToAssessment(results.get(0)));
    }

    @Override
    public List<AbilityAssessment> findByStudentId(String studentId) {
        String sql = "SELECT * FROM ability_assessments WHERE student_id = ?";
        return mapResultsToAssessments(jdbcUtil.executeQuery(sql, studentId));
    }

    @Override
    public List<AbilityAssessment> findBySubject(String subject) {
        String sql = "SELECT * FROM ability_assessments WHERE subject = ?";
        return mapResultsToAssessments(jdbcUtil.executeQuery(sql, subject));
    }

    @Override
    public List<AbilityAssessment> findByStudentIdAndSubject(String studentId, String subject) {
        String sql = "SELECT * FROM ability_assessments WHERE student_id = ? AND subject = ?";
        return mapResultsToAssessments(jdbcUtil.executeQuery(sql, studentId, subject));
    }

    @Override
    public List<AbilityAssessment> findByStudentIdAndSubjectOrderByAssessmentTimeDesc(String studentId, String subject) {
        String sql = "SELECT * FROM ability_assessments WHERE student_id = ? AND subject = ? ORDER BY assessment_time DESC";
        return mapResultsToAssessments(jdbcUtil.executeQuery(sql, studentId, subject));
    }

    @Override
    public AbilityAssessment findFirstByStudentIdAndSubjectOrderByAssessmentTimeDesc(String studentId, String subject) {
        String sql = "SELECT * FROM ability_assessments WHERE student_id = ? AND subject = ? ORDER BY assessment_time DESC LIMIT 1";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, studentId, subject);
        return results.isEmpty() ? null : mapRowToAssessment(results.get(0));
    }

    @Override
    public Object[] getAverageScoresBySubject(String subject) {
        String sql = "SELECT AVG(knowledge_dimension) as avg_knowledge, " +
                "AVG(application_dimension) as avg_application, " +
                "AVG(innovation_dimension) as avg_innovation " +
                "FROM ability_assessments WHERE subject = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, subject);
        
        if (results.isEmpty()) {
            return new Object[]{null, null, null};
        }
        
        Map<String, Object> row = results.get(0);
        return new Object[]{
                row.get("avg_knowledge"),
                row.get("avg_application"),
                row.get("avg_innovation")
        };
    }

    @Override
    public AbilityAssessment save(AbilityAssessment assessment) {
        if (assessment.getId() == null) {
            // 插入操作
            String sql = "INSERT INTO ability_assessments (student_id, subject, knowledge_dimension, " +
                    "application_dimension, innovation_dimension, assessment_details, recommendation, assessment_time) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            // 确保有评估时间
            if (assessment.getAssessmentTime() == null) {
                assessment.setAssessmentTime(LocalDateTime.now());
            }
            
            long id = jdbcUtil.executeInsert(sql,
                    assessment.getStudentId(),
                    assessment.getSubject(),
                    assessment.getKnowledgeDimension(),
                    assessment.getApplicationDimension(),
                    assessment.getInnovationDimension(),
                    assessment.getAssessmentDetails(),
                    assessment.getRecommendation(),
                    Timestamp.valueOf(assessment.getAssessmentTime()));
                    
            assessment.setId(id);
            return assessment;
        } else {
            // 更新操作
            String sql = "UPDATE ability_assessments SET student_id = ?, subject = ?, " +
                    "knowledge_dimension = ?, application_dimension = ?, innovation_dimension = ?, " +
                    "assessment_details = ?, recommendation = ?, assessment_time = ? " +
                    "WHERE id = ?";
            
            jdbcUtil.executeUpdate(sql,
                    assessment.getStudentId(),
                    assessment.getSubject(),
                    assessment.getKnowledgeDimension(),
                    assessment.getApplicationDimension(),
                    assessment.getInnovationDimension(),
                    assessment.getAssessmentDetails(),
                    assessment.getRecommendation(),
                    Timestamp.valueOf(assessment.getAssessmentTime()),
                    assessment.getId());
                    
            return assessment;
        }
    }

    @Override
    public void deleteById(Long id) {
        String sql = "DELETE FROM ability_assessments WHERE id = ?";
        jdbcUtil.executeUpdate(sql, id);
    }

    @Override
    public long count() {
        String sql = "SELECT COUNT(*) as count FROM ability_assessments";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    /**
     * 将查询结果映射为能力评估对象列表
     */
    private List<AbilityAssessment> mapResultsToAssessments(List<Map<String, Object>> results) {
        List<AbilityAssessment> assessments = new ArrayList<>();
        for (Map<String, Object> row : results) {
            assessments.add(mapRowToAssessment(row));
        }
        return assessments;
    }
    
    /**
     * 将单行结果映射为能力评估对象
     */
    private AbilityAssessment mapRowToAssessment(Map<String, Object> row) {
        AbilityAssessment assessment = new AbilityAssessment();
        assessment.setId(((Number) row.get("id")).longValue());
        assessment.setStudentId((String) row.get("student_id"));
        assessment.setSubject((String) row.get("subject"));
        
        if (row.get("knowledge_dimension") != null) {
            assessment.setKnowledgeDimension(((Number) row.get("knowledge_dimension")).floatValue());
        }
        
        if (row.get("application_dimension") != null) {
            assessment.setApplicationDimension(((Number) row.get("application_dimension")).floatValue());
        }
        
        if (row.get("innovation_dimension") != null) {
            assessment.setInnovationDimension(((Number) row.get("innovation_dimension")).floatValue());
        }
        
        assessment.setAssessmentDetails((String) row.get("assessment_details"));
        assessment.setRecommendation((String) row.get("recommendation"));
        
        Timestamp timestamp = (Timestamp) row.get("assessment_time");
        if (timestamp != null) {
            assessment.setAssessmentTime(timestamp.toLocalDateTime());
        }
        
        return assessment;
    }
} 